﻿using System.Collections;
using System.Data;
using DTO;
using System;
namespace DAO
{
    public class ThanhVienDAO:DataHelperSQLServer
    {
        public ThanhVienDAO()
        { }
        public ArrayList getDsThanhVien()
        {            
            string _query = "SELECT * FROM THANHVIEN";
            DataTable dt = ExecQuery(_query);
            ArrayList arr = ConvertDataTableToArrayList(dt);
            return arr;
        }
        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            ThanhVienDTO _tv = new ThanhVienDTO();
            try
            {
                int namhientai = int.Parse(DateTime.Now.Year.ToString());                
                //_tv.DaMat = bool.Parse(dt.Rows[i]["damat"].ToString());
                _tv.Doi = int.Parse(dt.Rows[i]["doi"].ToString());
                _tv.DuongDanHinh = dt.Rows[i]["duongdanhinhdaidien"].ToString();
                _tv.GioiTinh = bool.Parse(dt.Rows[i]["gioitinh"].ToString());
                _tv.MaThanhVien = int.Parse(dt.Rows[i]["mathanhvien"].ToString());
                _tv.QueQuan = dt.Rows[i]["QueQuan"].ToString();
                _tv.TieuSu = dt.Rows[i]["TieuSu"].ToString();
                string ngayMat = dt.Rows[i]["ngaymat"].ToString();
                if (ngayMat!="")
                {
                    _tv.NgayMat = DateTime.Parse(ngayMat);
                    _tv.NamMat = _tv.NgayMat.Year.ToString();

                }
                else
                {
                    _tv.NamMat = "-";
                }
                string ngaysinh = dt.Rows[i]["ngaysinh"].ToString();
                if (ngaysinh != "")
                {
                    _tv.NgaySinh = DateTime.Parse(ngaysinh);
                    _tv.NamSinh = _tv.NgaySinh.Year.ToString();
                }
                else {
                    _tv.NamSinh = "-";
                }
                _tv.TenThanhVien = dt.Rows[i]["tenthanhvien"].ToString();
                _tv.TonGiao = dt.Rows[i]["tongiao"].ToString();
                if (int.Parse(_tv.NamSinh)>=namhientai)
                {
                    _tv.NamSinh = "-";
                }
                else
                {
                    if (_tv.DaMat == false)
                    {
                        _tv.Tuoi = (namhientai - int.Parse(_tv.NamSinh)).ToString();
                    }
                    else
                    {
                        _tv.Tuoi = (int.Parse(_tv.NamMat) - int.Parse(_tv.NamSinh)).ToString();
                    }
                }
                
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            
            return (object)_tv;
        }
        public void insert(ThanhVienDTO _info)
        {
            string _insertCommand = "INSERT INTO HOCSINH VALUES('" +
                _info.MaThanhVien + "', '" +
                _info.TenThanhVien + "', '" +
                _info.NgaySinh.ToShortDateString() + "', " +                
                _info.Doi+" , "+
                _info.DaMat + " , '" +
                _info.NgayMat.ToShortDateString() + " ', " +
                _info.GioiTinh + " , '" +
                _info.TonGiao + "' , '" +
                _info.DuongDanHinh +"' , '"+
                _info.QueQuan +"' , '"+
                _info.DiaDiemMaiTang +  "' , '"+
                _info.TieuSu+     
                "')";

            ExecNonQuery(_insertCommand);
        }
        public void update(ThanhVienDTO _info)
        {
            int gioitinh = 0, damat = 0;
            if (_info.GioiTinh)
            {
                gioitinh = 1;
            }
            if (_info.DaMat)
            {
                damat = 1;
            }
            string _updateCommand = "UPDATE THANHVIEN SET TENTHANHVIEN='"+_info.TenThanhVien+
                                    "', NGAYSINH='"+_info.NgaySinh.ToShortDateString()+
                                    "', DOI="+_info.Doi+
                                    ", DAMAT="+damat+
                                    ", NGAYMAT='"+_info.NgayMat.ToShortDateString()+
                                    "', GIOITINH="+gioitinh+
                                    ", TONGIAO='"+_info.TonGiao+
                                    "', DUONGDANHINHDAIDIEN='"+_info.DuongDanHinh+
                                    "', QUEQUAN='"+_info.QueQuan+
                                    "', DIADIEMMAITANG='"+_info.DiaDiemMaiTang+
                                    "', TIEUSU='"+_info.TieuSu+
                                    "' WHERE MATHANHVIEN="+_info.MaThanhVien;
            ExecNonQuery(_updateCommand);
        }

        public void delete(int mathanhvien)
        {
            string _deleteCommand = "DELETE FROM THANHVIEN WHERE MATHANHVIEN = "+mathanhvien;
            ExecNonQuery(_deleteCommand);
        }
    }
    
}
